from typing import List
import logging
import pandas as pd
from pandas import DataFrame
from pandas import ExcelWriter
import time
import xlrd


class excelTools(object):
    logging = logging.getLogger("applog")

    def loadCheckExcel(self, excelName, sheetIndex=0) -> List:
        '''
        读取打卡机excel数据
        :param excelName: excel文件绝对路径
        :return:
        '''
        logging.info(f"开始处理{excelName}数据")
        # 处理excel中文乱码
        xlrd.Book.encoding = 'cp936'
        try:
            excel = xlrd.open_workbook(excelName)
            sheet = excel.sheets()[sheetIndex]
            rowsValuesList = []
            for rowId in range(1, sheet.nrows):
                filedValues = sheet.row_values(rowId, 1, 4)
                rowsValuesList.append(filedValues)
            return rowsValuesList

        except Exception as ex:
            logging.exception(ex)

    def loadRestExcel(self, excelName) -> List:
        '''
        读取请假excel数据
        :param excelName: excel文件绝对路径
        :return:
        '''
        logging.info(f"开始处理{excelName}数据")
        # 处理excel中文乱码
        try:
            excel = pd.read_excel(excelName, usecols=['申请人', '请假类型', '开始时间', '结束时间', '请假时长', '请假事由', '当前审批状态'])
            df = DataFrame(excel)
            # 过滤无效数据
            df = df[df['当前审批状态'] == '已通过']
            # 去掉状态字段
            df = df[['申请人', '请假类型', '开始时间', '结束时间', '请假时长', '请假事由']]
            rowsValuesList = []
            logging.info(df.values)
            for rowId in range(df.values.__len__()):
                rowValue = self.formater(df.values[rowId])
                rowsValuesList.append(rowValue.tolist())
            return rowsValuesList

        except Exception as ex:
            logging.exception(ex)

    def formater(self, values) -> list:
        # 开始时间
        if str(values[2]).find("上午") >= 0:
            temp = values[2].replace("上午", "08:30:00")
            data = time.strptime(temp, '%Y/%m/%d %H:%M:%S')
            values[2] = time.strftime('%Y-%m-%d %H:%M:%S', data)
        elif str(values[2]).find("下午") >= 0:
            temp = values[2].replace("下午", "12:00:00")
            data = time.strptime(temp, '%Y/%m/%d %H:%M:%S')
            values[2] = time.strftime('%Y-%m-%d %H:%M:%S', data)
        # 结束时间
        if str(values[3]).find("上午") >= 0:
            temp = values[3].replace("上午", "12:00:00")
            data = time.strptime(temp, '%Y/%m/%d %H:%M:%S')
            values[3] = time.strftime('%Y-%m-%d %H:%M:%S', data)
        elif str(values[3]).find("下午") >= 0:
            temp = values[3].replace("下午", "18:00:00")
            data = time.strptime(temp, '%Y/%m/%d %H:%M:%S')
            values[3] = time.strftime('%Y-%m-%d %H:%M:%S', data)
        # 请假时长
        leavetime = values[4]
        values[4] = float(leavetime[0: leavetime.find("天")])

        return values

    def outputExcel(self, summary, details):
        logging.info(f"开始输出")
        # 构建汇总
        summaryColumns = ['考勤号', '姓名', '应出勤天数', '请假类型', '请假时长(天)', '迟到/早退(次)', '周内加班小计(小时)', '本月加班餐补', '实际出勤天数']
        sheet1 = pd.DataFrame(data=summary, columns=summaryColumns)
        # 构建明细
        detailsColumns = ['考勤号', '姓名', '出勤日期', '上班时间', '下班时间', '迟到/早退(次)', '请假类型', '请假时长(天)', '加班时长(小时)', '加班餐补(次)']
        sheet2 = pd.DataFrame(data=details, columns=detailsColumns)
        with ExcelWriter("./out/output.xlsx") as writer:
            sheet1.to_excel(writer, sheet_name="汇总")
            sheet2.to_excel(writer, sheet_name="明细")
        return


if __name__ == '__main__':
    tools = excelTools()
    tools.outputExcel()
